from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import json

db = SQLAlchemy()

class HistoricalData(db.Model):
    """历史经营数据表 - 简化版本"""
    __tablename__ = 'historical_data'
    
    id = db.Column(db.Integer, primary_key=True)
    business_unit = db.Column(db.String(50), nullable=False, index=True)  # 业务单元：集团合计、人药制剂板块、人药原料板块、植保板块、动保板块
    metric_name = db.Column(db.String(50), nullable=False, index=True)    # 指标名称：销售收入、净利润、净利润率%
    year = db.Column(db.Integer, nullable=False, index=True)              # 年份
    value = db.Column(db.Float, nullable=False)                           # 指标值
    unit = db.Column(db.String(20), default='亿元')                       # 单位
    
    # 计算字段
    yoy_growth_rate = db.Column(db.Float)  # 同比增长率
    cagr = db.Column(db.Float)             # 复合年均增长率
    
    # 元数据
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 复合唯一约束
    __table_args__ = (
        db.UniqueConstraint('business_unit', 'metric_name', 'year', name='_business_metric_year_uc'),
    )
    
    def to_dict(self):
        return {
            'id': self.id,
            'business_unit': self.business_unit,
            'metric_name': self.metric_name,
            'year': self.year,
            'value': self.value,
            'unit': self.unit,
            'yoy_growth_rate': self.yoy_growth_rate,
            'cagr': self.cagr,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'updated_at': self.updated_at.isoformat() if self.updated_at else None
        }

class PredictionTask(db.Model):
    """预测任务表"""
    __tablename__ = 'prediction_tasks'
    
    id = db.Column(db.Integer, primary_key=True)
    task_id = db.Column(db.String(50), unique=True, nullable=False, index=True)
    task_name = db.Column(db.String(200), nullable=False)
    task_type = db.Column(db.String(50), nullable=False)  # single_product, multi_product, group_summary
    
    # 任务参数
    company_ids = db.Column(db.Text)  # JSON格式存储
    product_ids = db.Column(db.Text)  # JSON格式存储
    prediction_years = db.Column(db.Text)  # JSON格式存储
    description = db.Column(db.Text)  # 任务描述和额外参数
    
    # 执行状态
    status = db.Column(db.String(20), default='pending')  # pending, running, completed, failed
    progress = db.Column(db.Integer, default=0)
    current_step = db.Column(db.String(200))
    error_message = db.Column(db.Text)
    
    # 时间信息
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    started_at = db.Column(db.DateTime)
    completed_at = db.Column(db.DateTime)
    created_by = db.Column(db.String(50))
    
    def to_dict(self):
        return {
            'id': self.id,
            'task_id': self.task_id,
            'task_name': self.task_name,
            'task_type': self.task_type,
            'company_ids': json.loads(self.company_ids) if self.company_ids else [],
            'product_ids': json.loads(self.product_ids) if self.product_ids else [],
            'prediction_years': json.loads(self.prediction_years) if self.prediction_years else [],
            'description': self.description,
            'status': self.status,
            'progress': self.progress,
            'current_step': self.current_step,
            'error_message': self.error_message,
            'created_at': self.created_at.isoformat() if self.created_at else None,
            'started_at': self.started_at.isoformat() if self.started_at else None,
            'completed_at': self.completed_at.isoformat() if self.completed_at else None,
            'created_by': self.created_by
        }

class PredictionResult(db.Model):
    """预测结果表"""
    __tablename__ = 'prediction_results'
    
    id = db.Column(db.Integer, primary_key=True)
    task_id = db.Column(db.String(50), nullable=False, index=True)
    company_id = db.Column(db.String(50), nullable=False)
    company_name = db.Column(db.String(100), nullable=False)
    product_id = db.Column(db.String(50), nullable=False)
    product_name = db.Column(db.String(100), nullable=False)
    prediction_year = db.Column(db.Integer, nullable=False)
    
    # 预测数据
    predicted_sales_volume = db.Column(db.Float, nullable=False)
    predicted_avg_price = db.Column(db.Float, nullable=False)
    predicted_sales_amount = db.Column(db.Float, nullable=False)
    predicted_unit_cost = db.Column(db.Float, nullable=False)
    predicted_unit_profit = db.Column(db.Float, nullable=False)
    
    # 差异分析
    volume_variance = db.Column(db.Float)  # 量差
    price_variance = db.Column(db.Float)   # 价差
    cost_variance = db.Column(db.Float)    # 成本差异
    total_variance = db.Column(db.Float)   # 总差异
    
    # AI分析
    influence_factors_analysis = db.Column(db.Text)  # 影响因素分析
    confidence_score = db.Column(db.Float)           # 置信度分数
    
    # 集团预测专用字段
    comprehensive_rate = db.Column(db.Float)         # 综合增长率（集团预测使用）
    
    # 状态信息
    status = db.Column(db.String(20), default='completed')
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    
    def to_dict(self):
        return {
            'id': self.id,
            'task_id': self.task_id,
            'company_id': self.company_id,
            'company_name': self.company_name,
            'product_id': self.product_id,
            'product_name': self.product_name,
            'prediction_year': self.prediction_year,
            'predicted_sales_volume': self.predicted_sales_volume,
            'predicted_avg_price': self.predicted_avg_price,
            'predicted_sales_amount': self.predicted_sales_amount,
            'predicted_unit_cost': self.predicted_unit_cost,
            'predicted_unit_profit': self.predicted_unit_profit,
            'volume_variance': self.volume_variance,
            'price_variance': self.price_variance,
            'cost_variance': self.cost_variance,
            'total_variance': self.total_variance,
            'influence_factors_analysis': self.influence_factors_analysis,
            'confidence_score': self.confidence_score,
            'comprehensive_rate': self.comprehensive_rate,
            'status': self.status,
            'created_at': self.created_at.isoformat() if self.created_at else None
        }

class IndustryData(db.Model):
    """行业数据表"""
    __tablename__ = 'industry_data'
    
    id = db.Column(db.Integer, primary_key=True)
    source = db.Column(db.String(100), nullable=False)  # 数据来源
    title = db.Column(db.String(200), nullable=False)   # 标题
    content = db.Column(db.Text)                        # 内容
    url = db.Column(db.String(500))                     # 原始URL
    industry = db.Column(db.String(50))                 # 行业分类
    data_type = db.Column(db.String(50))                # 数据类型
    
    # 提取的关键信息
    market_size = db.Column(db.Float)      # 市场规模
    growth_rate = db.Column(db.Float)      # 增长率
    cagr = db.Column(db.Float)             # 复合年均增长率
    key_metrics = db.Column(db.Text)       # 关键指标（JSON格式）
    
    # 元数据
    publish_date = db.Column(db.Date)
    crawl_date = db.Column(db.DateTime, default=datetime.utcnow)
    status = db.Column(db.String(20), default='active')  # active, archived
    
    def to_dict(self):
        return {
            'id': self.id,
            'source': self.source,
            'title': self.title,
            'content': self.content,
            'url': self.url,
            'industry': self.industry,
            'data_type': self.data_type,
            'market_size': self.market_size,
            'growth_rate': self.growth_rate,
            'cagr': self.cagr,
            'key_metrics': json.loads(self.key_metrics) if self.key_metrics else {},
            'publish_date': self.publish_date.isoformat() if self.publish_date else None,
            'crawl_date': self.crawl_date.isoformat() if self.crawl_date else None,
            'status': self.status
        }

